<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_ivr_cs2_jr">
  <title>Configuring Database Authorization</title>
  <shortdesc>Describes how to restrict authorization access to database data at the user level by
    using roles and permissions.</shortdesc>
  <topic id="topic_k35_qtx_kr">
    <title>Access Permissions and Roles</title>
    <body>
      <p>Greenplum Database manages database access permissions using <i>roles</i>. The concept of
        roles subsumes the concepts of users and groups. A role can be a database user, a group, or
        both. Roles can own database objects (for example, tables) and can assign privileges on
        those objects to other roles to control access to the objects. Roles can be members of other
        roles, thus a member role can inherit the object privileges of its parent role. </p>
      <p>Every Greenplum Database system contains a set of database roles (users and groups). Those
        roles are separate from the users and groups managed by the operating system on which the
        server runs. However, for convenience you may want to maintain a relationship between
        operating system user names and Greenplum Database role names, since many of the client
        applications use the current operating system user name as the default. </p>
      <p> In Greenplum Database, users log in and connect through the master instance, which
        verifies their role and access privileges. The master then issues out commands to the
        segment instances behind the scenes using the currently logged in role. </p>
      <p> Roles are defined at the system level, so they are valid for all databases in the system. </p>
      <p> To bootstrap the Greenplum Database system, a freshly initialized system always contains
        one predefined superuser role (also referred to as the system user). This role will have the
        same name as the operating system user that initialized the Greenplum Database system.
        Customarily, this role is named <codeph>gpadmin</codeph>. To create more roles you first
        must connect as this initial role. </p>
    </body>
  </topic>
  <topic id="managing_obj_priv">
    <title>Managing Object Privileges</title>
    <body>
      <p> When an object (table, view, sequence, database, function, language, schema, or
        tablespace) is created, it is assigned an owner. The owner is normally the role that
        ran the creation statement. For most kinds of objects, the initial state is that only
        the owner (or a superuser) can do anything with the object. To allow other roles to use it,
        privileges must be granted. Greenplum Database supports the following privileges for each
        object type: </p>
      <table>
        <tgroup cols="0">
          <colspec colwidth="50*" align="left"/>
          <colspec colwidth="50*" align="left"/>
          <thead>
            <row>
              <entry> Object Type </entry>
              <entry> Privileges </entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry> Tables, Views, Sequences </entry>
              <entry>
                <sl>
                  <sli>
                    <codeph>SELECT</codeph>
                  </sli>
                  <sli>
                    <codeph>INSERT</codeph>
                  </sli>
                  <sli>
                    <codeph>UPDATE</codeph>
                  </sli>
                  <sli>
                    <codeph>DELETE</codeph>
                  </sli>
                  <sli>
                    <codeph>RULE</codeph>
                  </sli>
                  <sli>
                    <codeph>ALL</codeph>
                  </sli>
                </sl>
              </entry>
            </row>
            <row>
              <entry> External Tables </entry>
              <entry>
                <sl>
                  <sli>
                    <codeph>SELECT</codeph>
                  </sli>
                  <sli>
                    <codeph>RULE</codeph>
                  </sli>
                  <sli>
                    <codeph>ALL</codeph>
                  </sli>
                </sl>
              </entry>
            </row>
            <row>
              <entry> Databases </entry>
              <entry>
                <sl>
                  <sli>
                    <codeph>CONNECT</codeph>
                  </sli>
                  <sli>
                    <codeph>CREATE</codeph>
                  </sli>
                  <sli>
                    <codeph>TEMPORARY | TEMP</codeph>
                  </sli>
                  <sli>
                    <codeph>ALL</codeph>
                  </sli>
                </sl>
              </entry>
            </row>
            <row>
              <entry> Functions </entry>
              <entry>
                <codeph>EXECUTE</codeph>
              </entry>
            </row>
            <row>
              <entry> Procedural Languages </entry>
              <entry>
                <codeph>USAGE</codeph>
              </entry>
            </row>
            <row>
              <entry> Schemas </entry>
              <entry>
                <sl>
                  <sli>
                    <codeph>CREATE</codeph>
                  </sli>
                  <sli>
                    <codeph>USAGE</codeph>
                  </sli>
                  <sli>
                    <codeph>ALL</codeph>
                  </sli>
                </sl>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <p> Privileges must be granted for each object individually. For example, granting
          <codeph>ALL</codeph> on a database does not grant full access to the objects within that
        database. It only grants all of the database-level privileges (<codeph>CONNECT</codeph>,
          <codeph>CREATE</codeph>, <codeph>TEMPORARY</codeph>) to the database itself. </p>
      <p> Use the <codeph>GRANT</codeph> SQL command to give a specified role privileges on an
        object. For example: <codeblock>=# GRANT INSERT ON mytable TO jsmith; </codeblock></p>
      <p> To revoke privileges, use the <codeph>REVOKE</codeph> command. For example:
        <codeblock>=# REVOKE ALL PRIVILEGES ON mytable FROM jsmith; </codeblock></p>
      <p> You can also use the <codeph>DROP OWNED</codeph> and <codeph>REASSIGN OWNED</codeph>
        commands for managing objects owned by deprecated roles. (Note: only an object's owner or a
        superuser can drop an object or reassign ownership.) For example:
        <codeblock> =# REASSIGN OWNED BY sally TO bob;
 =# DROP OWNED BY visitor; </codeblock></p>
    </body>
  </topic>
  <topic id="using-ssh-256">
    <title>Using SSH-256 Encryption</title>
    <body>
      <p>Greenplum Database access control corresponds roughly to the Orange Book 'C2' level of
        security, not the 'B1' level. Greenplum Database currently supports access privileges at the
        object level. Greenplum Database does not support row-level access or row-level, labeled security. </p>
      <p>You can simulate row-level access by using views to restrict the
        rows that are selected. You can simulate row-level labels by adding an extra column
        to the table to store sensitivity information, and then using views to control row-level
        access based on this column. You can then grant roles access to the views rather than the
        base table. While these workarounds do not provide the same as "B1" level security, they may
        still be a viable alternative for many organizations. </p>
      <p> To use SHA-256 encryption, you must set a parameter either at the system or the session
        level. This section outlines how to use a server parameter to implement SHA-256 encrypted
        password storage. Note that in order to use SHA-256 encryption for storage, the client
        authentication method must be set to password rather than the default, MD5. (See <xref
          href="Authenticate.xml#topic_fzv_wb2_jr/config_ssl_client_conn"/> for more details.) This
        means that the password is transmitted in clear text over the network, so we highly
        recommend that you set up SSL to encrypt the client server communication channel. </p>
      <p> You can set your chosen encryption method system-wide or on a per-session basis. The
        available encryption methods are SHA-256  and MD5 (for backward compatibility). </p>
    </body>
    <topic id="system-wide">
      <title>Setting Encryption Method System-wide</title>
      <body>
        <p>To set the <codeph>password_hash_algorithm</codeph> server parameter on a complete
          Greenplum system (master and its segments): <ol id="ol_hcg_hw2_jr">
            <li> Log in to your Greenplum Database instance as a superuser. </li>
            <li> Execute <codeph>gpconfig</codeph> with the <codeph>password_hash_algorithm</codeph>
              set to
              SHA-256:<codeblock>$ gpconfig -c password_hash_algorithm -v 'SHA-256' </codeblock></li>
            <li> Verify the setting: <codeblock>$ gpconfig -s</codeblock><p> You will see:
                <codeblock>Master value: SHA-256
Segment value: SHA-256 </codeblock></p></li>
          </ol></p>
      </body>
    </topic>
    <topic id="individual_session">
      <title>Setting Encryption Method for an Individual Session</title>
      <body>
        <p> To set the <codeph>password_hash_algorithm</codeph> server parameter for an individual
          session: </p>
        <ol id="ol_iv1_cvx_kr">
          <li> Log in to your Greenplum Database instance as a superuser. </li>
          <li> Set the <codeph>password_hash_algorithm</codeph> to
            SHA-256:<codeblock># set password_hash_algorithm = 'SHA-256'
  </codeblock></li>
          <li> Verify the setting: <codeblock># show password_hash_algorithm;</codeblock><p> You
              will see: </p><codeblock>SHA-256 </codeblock></li>
        </ol>
        <p> Following is an example of how the new setting works: </p>
        <ol id="ol_ex1_cvx_kr">
          <li> Log in as a super user and verify the password hash algorithm
            setting:<codeblock># show password_hash_algorithm 
 password_hash_algorithm 
 ------------------------------- 
 SHA-256</codeblock></li>
          <li> Create a new role with password that has login privileges.
            <codeblock>create role testdb with password 'testdb12345#' LOGIN; </codeblock></li>
          <li> Change the client authentication method to allow for storage of SHA-256 encrypted
              passwords:<p> Open the <codeph>pg_hba.conf</codeph> file on the master and add the
              following line: </p><codeblock>host all testdb 0.0.0.0/0 password
  </codeblock></li>
          <li> Restart the cluster. </li>
          <li> Log in to the database as the user just created, <codeph>testdb</codeph>.
            <codeblock>psql -U testdb</codeblock></li>
          <li> Enter the correct password at the prompt. </li>
          <li> Verify that the password is stored as a SHA-256 hash. <p> Password hashes are stored
              in <codeph>pg_authid.rolpasswod</codeph>. </p></li>
          <li> Log in as the super user. </li>
          <li> Execute the following query:
            <codeblock>
    # SELECT rolpassword FROM pg_authid WHERE rolname = 'testdb';
    Rolpassword
    -----------
    sha256&lt;64 hexadecimal characters&gt;
  </codeblock></li>

        </ol>
      </body>
    </topic>
  </topic>
  <topic id="time-based-restriction">
    <title>Restricting Access by Time</title>
    <body>
      <p> Greenplum Database enables the administrator to restrict access to certain times by role.
        Use the <codeph>CREATE ROLE</codeph> or <codeph>ALTER ROLE</codeph> commands to specify
        time-based constraints. </p>
      <p> Access can be restricted by day or by day and time. The constraints are removable without
        deleting and recreating the role. </p>
      <p> Time-based constraints only apply to the role to which they are assigned. If a role is a
        member of another role that contains a time constraint, the time constraint is not
        inherited. </p>
      <p> Time-based constraints are enforced only during login. The <codeph>SET ROLE</codeph> and
          <codeph>SET SESSION AUTHORIZATION</codeph> commands are not affected by any time-based
        constraints. </p>
      <p> Superuser or <codeph>CREATEROLE</codeph> privileges are required to set time-based
        constraints for a role. No one can add time-based constraints to a superuser. </p>
      <p> There are two ways to add time-based constraints. Use the keyword <codeph>DENY</codeph> in
        the <codeph>CREATE ROLE</codeph> or <codeph>ALTER ROLE</codeph> command followed by one of
        the following. <ul id="ul_bjq_jz2_jr">
          <li> A day, and optionally a time, when access is restricted. For example, no access on
            Wednesdays. </li>
          <li> An interval—that is, a beginning and ending day and optional time—when access is
            restricted. For example, no access from Wednesday 10 p.m. through Thursday at 8 a.m.
          </li>
        </ul></p>
      <p> You can specify more than one restriction; for example, no access Wednesdays at any time
        and no access on Fridays between 3:00 p.m. and 5:00 p.m.  </p>
      <p> There are two ways to specify a day. Use the word <codeph>DAY</codeph> followed by either
        the English term for the weekday, in single quotation marks, or a number between 0 and 6, as
        shown in the table below. </p>
      <table id="table_az1_cvx_kr">
        <tgroup cols="0">
          <colspec colwidth="50*" align="left"/>
          <colspec colwidth="50*" align="left"/>
          <thead>
            <row>
              <entry> English Term </entry>
              <entry> Number </entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry> DAY 'Sunday' </entry>
              <entry> DAY 0 </entry>
            </row>
            <row>
              <entry> DAY 'Monday' </entry>
              <entry> DAY 1 </entry>
            </row>
            <row>
              <entry> DAY 'Tuesday' </entry>
              <entry> DAY 2 </entry>
            </row>
            <row>
              <entry> DAY 'Wednesday' </entry>
              <entry> DAY 3 </entry>
            </row>
            <row>
              <entry> DAY 'Thursday' </entry>
              <entry> DAY 4 </entry>
            </row>
            <row>
              <entry> DAY 'Friday' </entry>
              <entry> DAY 5 </entry>
            </row>
            <row>
              <entry> DAY 'Saturday' </entry>
              <entry> DAY 6 </entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <p> A time of day is specified in either 12- or 24-hour format. The word <codeph>TIME</codeph>
        is followed by the specification in single quotation marks. Only hours and minutes are
        specified and are separated by a colon ( : ). If using a 12-hour format, add
          <codeph>AM</codeph> or <codeph>PM</codeph> at the end. The following examples show various
        time specifications. </p>
      <codeblock>TIME '14:00'     # 24-hour time implied
TIME '02:00 PM'  # 12-hour time specified by PM 
TIME '02:00'     # 24-hour time implied. This is equivalent to TIME '02:00 AM'. </codeblock>
      <note type="important">Time-based authentication is enforced with the server time. Timezones
        are disregarded. </note>
      <p> To specify an interval of time during which access is denied, use two day/time
        specifications with the words <codeph>BETWEEN</codeph> and <codeph>AND</codeph>, as shown.
          <codeph>DAY</codeph> is always required. </p>
      <codeblock>BETWEEN DAY 'Monday' AND DAY 'Tuesday' 

BETWEEN DAY 'Monday' TIME '00:00' AND
        DAY 'Monday' TIME '01:00'

BETWEEN DAY 'Monday' TIME '12:00 AM' AND
        DAY 'Tuesday' TIME '02:00 AM'

BETWEEN DAY 'Monday' TIME '00:00' AND
        DAY 'Tuesday' TIME '02:00'
        DAY 2 TIME '02:00'</codeblock>
      <p>The last three statements are equivalent. </p>
      <note>Intervals of days cannot wrap past Saturday.</note>
      <p>The following syntax is not correct:
        <codeblock>DENY BETWEEN DAY 'Saturday' AND DAY 'Sunday'</codeblock>
      </p>
      <p> The correct specification uses two DENY clauses, as follows: </p>
      <codeblock>DENY DAY 'Saturday'
DENY DAY 'Sunday'</codeblock>
      <p> The following examples demonstrate creating a role with time-based constraints and
        modifying a role to add time-based constraints. Only the statements needed for time-based
        constraints are shown. For more details on creating and altering roles see the descriptions
        of <codeph>CREATE ROLE</codeph> and <codeph>ALTER ROLE</codeph> in the <i>Greenplum
          Database Reference Guide</i>. </p>
      <section>
        <title> Example 1 – Create a New Role with Time-based Constraints </title>
        <p> No access is allowed on weekends. </p>
        <codeblock> CREATE ROLE generaluser
 DENY DAY 'Saturday'
 DENY DAY 'Sunday'
 ... </codeblock>
      </section>
      <section>
        <title>Example 2 – Alter a Role to Add Time-based Constraints </title>
        <p> No access is allowed every night between 2:00 a.m. and 4:00 a.m. </p>
        <codeblock>ALTER ROLE generaluser
 DENY BETWEEN DAY 'Monday' TIME '02:00' AND DAY 'Monday' TIME '04:00'
 DENY BETWEEN DAY 'Tuesday' TIME '02:00' AND DAY 'Tuesday' TIME '04:00'
 DENY BETWEEN DAY 'Wednesday' TIME '02:00' AND DAY 'Wednesday' TIME '04:00'
 DENY BETWEEN DAY 'Thursday' TIME '02:00' AND DAY 'Thursday' TIME '04:00'
 DENY BETWEEN DAY 'Friday' TIME '02:00' AND DAY 'Friday' TIME '04:00'
 DENY BETWEEN DAY 'Saturday' TIME '02:00' AND DAY 'Saturday' TIME '04:00'
 DENY BETWEEN DAY 'Sunday' TIME '02:00' AND DAY 'Sunday' TIME '04:00'
  ... </codeblock>
      </section>
      <section>
        <title>Excample 3 – Alter a Role to Add Time-based Constraints </title>
        <p> No access is allowed Wednesdays or Fridays between 3:00 p.m. and 5:00 p.m. </p>
        <codeblock>ALTER ROLE generaluser
 DENY DAY 'Wednesday'
 DENY BETWEEN DAY 'Friday' TIME '15:00' AND DAY 'Friday' TIME '17:00'
 </codeblock>
      </section>
    </body>
  </topic>
  <topic id="drop_timebased_restriction">
    <title> Dropping a Time-based Restriction </title>
    <body>
      <p> To remove a time-based restriction, use the ALTER ROLE command. Enter the keywords DROP
        DENY FOR followed by a day/time specification to drop. </p>
      <codeblock>DROP DENY FOR DAY 'Sunday' </codeblock>
      <p> Any constraint containing all or part of the conditions in a DROP clause is removed. For
        example, if an existing constraint denies access on Mondays and Tuesdays, and the DROP
        clause removes constraints for Mondays, the existing constraint is completely dropped. The
        DROP clause completely removes all constraints that overlap with the constraint in the drop
        clause. The overlapping constraints are completely removed even if they contain more
        restrictions that the restrictions mentioned in the DROP clause. </p>
      <p> Example 1 - Remove a Time-based Restriction from a Role </p>
      <codeblock> ALTER ROLE generaluser
 DROP DENY FOR DAY 'Monday'
    ... </codeblock>
      <p> This statement would remove all constraints that overlap with a Monday constraint for the
        role <codeph>generaluser</codeph> in Example 2, even if there are additional
        constraints.</p>
    </body>
  </topic>
</topic>
